package cn.iune.erp.inventory;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import javax.persistence.NoResultException;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.justep.baas.Utils;
import com.justep.baas.data.DataUtils;
import com.justep.baas.data.Table;
import com.justep.baas.data.Transform;

import cn.iune.common.base.BaseJPAImpl;


@Repository
public class StorageLedgerDetailDaoJPAImpl extends BaseJPAImpl<StorageLedgerDetail> implements IStorageLedgerDetailDao {

	private String kSql;
//	@PersistenceContext 
//	private EntityManager em ;


	@Override
	public void insert(StorageLedgerDetail obj) {
		em.persist(obj);
	}

	@Override
	public void delete(String fID) {
		em.remove(em.getReference(StorageLedgerDetail.class, fID));
	}

	@Override
	public StorageLedgerDetail update(StorageLedgerDetail obj) {
		StorageLedgerDetail objNew = em.merge(obj);
		return objNew;
	}

	@Override
	public StorageLedgerDetail getByID(String fID) {
		StorageLedgerDetail singleResult = em.find(StorageLedgerDetail.class, fID);
		return singleResult;
	}

	@Override
	public List<StorageLedgerDetail> getAll() {
		Query query = em.createNamedQuery("StorageLedgerDetail.findAll");
		@SuppressWarnings("unchecked")
		List<StorageLedgerDetail> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public List<StorageLedgerDetail> getByMasterID(String fMasterID) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageLedgerDetail FROM StorageLedgerDetail StorageLedgerDetail "
				+ " WHERE StorageLedgerDetail.fMasterID=:fMasterID "
				+ " ORDER BY serialNo ASC"
				;
		Query query = em.createQuery(kSql);
		query.setParameter("fMasterID", fMasterID);
		@SuppressWarnings("unchecked")
		List<StorageLedgerDetail> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public StorageLedgerDetail getByNo(String recordNo, String serialNo, String revOpay, String enterpriseNo) {
		//1、定义sql语句
		kSql="SELECT StorageLedgerDetail FROM StorageLedgerDetail StorageLedgerDetail "
				+ " WHERE ( StorageLedgerDetail.recordNo = :recordNo ) "
				+ " AND ( StorageLedgerDetail.serialNo = :serialNo ) "
				+ " AND ( StorageLedgerDetail.revOpay = :revOpay ) "
				+ " AND ( StorageLedgerDetail.enterpriseNo = :enterpriseNo ) "
				;
		
		Query query = em.createQuery(kSql);
		query.setParameter("recordNo", recordNo);
		query.setParameter("serialNo", serialNo);
		query.setParameter("revOpay", revOpay);
		query.setParameter("enterpriseNo", enterpriseNo);
		StorageLedgerDetail singleResult = null;
		try{
			singleResult = (StorageLedgerDetail) query.getSingleResult();
		}catch(NoResultException e){
			//捕获没有结果的异常
			//避免抛出异常而中断对前台的相应。
		}finally{
			
		}
		return singleResult;
	}

	
	@Override
	public JSONObject create(JSONObject data) {
		//缺省列
		String defColstr = Utils.getDefCols(StorageLedgerDetail.class);
		data.put("defColstr", defColstr);
		return Utils.newData(data);

	}

	@Override
	public JSONObject query(JSONObject data) throws SQLException {
		
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String fMasterID = data.getString("fMasterID");
		
//		String userId = data.getString("userId");
//		String enterpriseNo = data.getString("enterpriseNo");
//		log.info("userId-->"+userId);
//		log.info("enterpriseNo-->"+enterpriseNo);

		//		log.info("limit-->"+limit);
//		log.info("offset-->"+offset);
//		log.info("filters-->"+filters);
//		log.info("sqlParams-->"+params);
		/*
		limit-->15
		offset-->0
		filters-->[unitCode LIKE ? OR unitName LIKE ? OR unitType LIKE ?]
		sqlParams-->[%3%, %3%, %3%]
		*/		
		
		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT StorageLedgerDetail FROM StorageLedgerDetail StorageLedgerDetail %s %s ";
		String select = "StorageLedgerDetail.*"
				+ ",if(StorageLedgerDetail.itemProperty is null,StorageLedgerDetail.itemName,CONCAT(StorageLedgerDetail.itemName,'【',StorageLedgerDetail.itemProperty,'】')) as itemDescribe"
				+ ",if(StorageLedgerDetail.revOpay='I',StorageLedgerDetail.itemQuantity,0) as accountQuantityI"
				+ ",if(StorageLedgerDetail.revOpay='O',StorageLedgerDetail.itemQuantity,0) as accountQuantityO"
				+ ",if(StorageLedgerDetail.revOpay='I',StorageLedgerDetail.pieceQuantity,0) as pieceQuantityI"
				+ ",if(StorageLedgerDetail.revOpay='O',StorageLedgerDetail.pieceQuantity,0) as pieceQuantityO"

				;
		
		String from = "StorageLedgerDetail StorageLedgerDetail "
				;

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		//处理模块查询
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件
			filters.add("recordNo LIKE ? OR itemName LIKE ?  OR StorageLedgerDetail.comments LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 3; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("StorageLedgerDetail.fID='"+id+"'");
		}
		//处理fMasterID
//		System.out.println("fMasterID-->"+fMasterID);
		if (Utils.isNotEmptyString(fMasterID)) {
			filters.add("StorageLedgerDetail.fMasterID='"+fMasterID+"'");
		}
	
		//处理条件过滤
		JSONArray filterList = data.getJSONArray("filters"); // 列定义
		if(filterList!=null && filterList.size()>0){
			for(int i=0;i<filterList.size();i++){
				JSONObject jsonFilter = filterList.getJSONObject(i);
//				System.out.println("jsonFilter.getString(\"filter\")-->"+jsonFilter.getString("filter"));
				filters.add(jsonFilter.getString("filter"));
			}
		}
		
		String where = "WHERE 1=1 ";
		
		if(filters != null && filters.size() > 0){
			where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
		}
		
//		System.out.println("where-->"+where);
		
		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		System.out.println("sql-->"+sql);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT StorageLedgerDetail.fID FROM StorageLedgerDetail StorageLedgerDetail    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);

		table.setTotal(Integer.parseInt(total.toString()));
		JSONObject json = Transform.tableToJson(table);
		//////////////////////////////////自定义聚合数据/////////////////////////////////
		//////////////////////////////////////////////////////////////////////////////
		String aggregateSelect = "";
		
		JSONObject aggregateColumns = data.getJSONObject("aggregateColumns"); // 聚合列定义
//		System.out.println("aggregateColumns-->"+aggregateColumns);
		if(aggregateColumns!=null){
			
			Set<String> keys = aggregateColumns.keySet();
			int index = 0;
			for(String key:keys){
				if(index==0){
					aggregateSelect = aggregateSelect + "SUM(" + key + ") as " + aggregateColumns.getJSONObject(key).getString("name");
				}else{
					aggregateSelect = aggregateSelect + ",SUM(" + key + ") as " + aggregateColumns.getJSONObject(key).getString("name");
				}
				
				index++;
			}
			
//			aggregateSelect = "SUM(StorageItem.quantityL) as sumQuantityL,SUM(CONVERT(FileItem.costingAmountPer*StorageItem.quantityL,decimal(38,2))) as sumCostingAmount,SUM(StorageItem.pieceQuantityL) as sumPieceQuantityL";
			
		}
		
//		String aggregate = data.getString("aggregate");
//		if(aggregate!=null){
//			if(aggregateSelect.equals("")){
//				aggregateSelect = aggregate;
//			}else{
//				aggregateSelect = aggregateSelect + "," + aggregate;
//			}
//		}
		
//		System.out.println("aggregateSelect-->"+aggregateSelect);
		
		if(!aggregateSelect.equals("")){
			//如果select不为空
			
			String aggregateSql = String.format(format, aggregateSelect, from, where, "");
//			System.out.println("aggregateSql-->"+aggregateSql);
			Table aggregateTable = DataUtils.queryData(conn, aggregateSql, sqlParams, aggregateColumns, 0, table.getTotal());
//			Row row = aggregateTable.getRows().get(0);
//			JSONObject aggregateValues = Transform.rowToJson(aggregateTable, row);
			JSONObject jo = Transform.tableToJson(aggregateTable);
			JSONArray jarray = jo.getJSONArray("rows");
			JSONObject aggregateValues = new JSONObject();
			if(jarray.size()>0){
				aggregateValues = jarray.getJSONObject(0);
			}
//			System.out.println("aggregateValues-->"+aggregateValues);
			//jb-->{"userdata":{"recordState":"none"},"costingAmount":{"value":"599517613.43"},"costingAmountPer":{"value":"59957.893727"}}
			json.put("aggregateValues", aggregateValues);
		}
		//////////////////////////////////////////////////////////////////////////////////
		//////////////////////////////////////////////////////////////////////////////////
		
		//关闭连接
		conn.close();
		
		return json;
	}

	@Override
	public List<StorageLedgerDetail> getByMasterID(String fMasterID, String billType) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageLedgerDetail"
				+ " FROM StorageLedgerDetail StorageLedgerDetail "
				+ " WHERE StorageLedgerDetail.fMasterID = :fMasterID "
				+ " AND StorageLedgerDetail.billType = :billType "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("billType", billType);
		query.setParameter("fMasterID", fMasterID);
		@SuppressWarnings("unchecked")
		List<StorageLedgerDetail> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public List<StorageLedgerDetail> getListByItemID(String itemID, Date dateStart, Date dateEnd,
			String enterpriseNo) {
		// TODO Auto-generated method stub
		
		/*
		XSCK	销售发货单：
		XSRK	销售退货单：
		QTCK	其他出库单：
		XHCK	销毁单：（仓库未非存货仓）需被排除掉
		SCFL	生产领料单：
		SCTL	生产退料单：
		WWFL	委外领料单：
		WWTL	委外退料单：
		
		DBDJ	调拨单：
		BFDJ	报废单：
		JCDJ	借出单：
		JCGH	借出归还单：
		JRDJ	借入单：
		JRGH	介入归还单：
		*/
		kSql="SELECT StorageLedgerDetail"
				+ " FROM StorageLedgerDetail StorageLedgerDetail "

				+ " WHERE StorageLedgerDetail.itemID = :itemID "
				+ " AND StorageLedgerDetail.orderDate >= :dateStart "
				+ " AND StorageLedgerDetail.orderDate <= :dateEnd "
				+ " AND StorageLedgerDetail.enterpriseNo = :enterpriseNo "
				
				;
		
		Query query = em.createQuery(kSql);
		query.setParameter("itemID", itemID);
		query.setParameter("dateStart", dateStart);
		query.setParameter("dateEnd", dateEnd);
		query.setParameter("enterpriseNo", enterpriseNo);
		@SuppressWarnings("unchecked")
		List<StorageLedgerDetail> resultList = query.getResultList();
		return resultList;
	}

	//成本核算相关的领料单列表
	@Override
	public List<StorageLedgerDetail> getCostingLLDList(String workNo, Date dateStart, Date dateEnd, String enterpriseNo) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageLedgerDetail"
				+ " FROM StorageLedgerDetail StorageLedgerDetail "

				+ " WHERE  StorageLedgerDetail.workNo = :workNo  "
				+ " AND StorageLedgerDetail.orderDate >= :dateStart "
				+ " AND StorageLedgerDetail.orderDate <= :dateEnd "
				+ " AND StorageLedgerDetail.enterpriseNo = :enterpriseNo "
				
				+ " AND ( StorageLedgerDetail.billType = 'SCFL'" //生产发料单
				+ " OR StorageLedgerDetail.billType = 'SCTL'" //生产退料单
				+ " OR StorageLedgerDetail.billType = 'WWFL'" //委外发料单
				+ " OR StorageLedgerDetail.billType = 'WWTL'" //委外退料单
				+ ")"
				
				;
		Query query = em.createQuery(kSql);
		query.setParameter("workNo", workNo);
		query.setParameter("dateStart", dateStart);
		query.setParameter("dateEnd", dateEnd);
		query.setParameter("enterpriseNo", enterpriseNo);
		@SuppressWarnings("unchecked")
		List<StorageLedgerDetail> resultList = query.getResultList();
		return resultList;
	}

	//成本核算相关的入库单列表（相当于成本来源单列表）
	@Override
	public List<StorageLedgerDetail> getCostingRKDList(Date dateStart, Date dateEnd, String enterpriseNo) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageLedgerDetail"
				+ " FROM StorageLedgerDetail StorageLedgerDetail "

				+ " WHERE  StorageLedgerDetail.orderDate >= :dateStart "
				+ " AND StorageLedgerDetail.orderDate <= :dateEnd "
				+ " AND StorageLedgerDetail.enterpriseNo = :enterpriseNo "
				
				+ " AND ( StorageLedgerDetail.billType = 'WWRK'" //委外入库单
				+ " OR StorageLedgerDetail.billType = 'WWCK'" //委外退货单
				
				+ " OR StorageLedgerDetail.billType = 'SCRK'" //生产入库单
				+ " OR StorageLedgerDetail.billType = 'SCCK'" //生产退货单
				
				+ " OR StorageLedgerDetail.billType = 'GYRK'" //工艺入库单
				+ " OR StorageLedgerDetail.billType = 'GYCK'" //工艺退货单
				
				+ ")"
				;
		
		Query query = em.createQuery(kSql);
		query.setParameter("dateStart", dateStart);
		query.setParameter("dateEnd", dateEnd);
		query.setParameter("enterpriseNo", enterpriseNo);
		@SuppressWarnings("unchecked")
		List<StorageLedgerDetail> resultList = query.getResultList();
		return resultList;
	}

	//成本核算相关的委外单列表
	@Override
	public List<StorageLedgerDetail> getCostingWWDList(Date dateStart, Date dateEnd, String enterpriseNo) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageLedgerDetail FROM StorageLedgerDetail StorageLedgerDetail "

				+ " WHERE StorageLedgerDetail.orderDate >= :dateStart "
				+ " AND StorageLedgerDetail.orderDate <= :dateEnd "
				+ " AND StorageLedgerDetail.enterpriseNo = :enterpriseNo "
				
				+ " AND ( StorageLedgerDetail.billType = 'WWRK'" //委外入库单
				+ " OR StorageLedgerDetail.billType = 'WWCK'" //委外退货单
				
				+ " OR (StorageLedgerDetail.billType = 'GYRK' AND StorageLedgerDetail.storageTypeD = 'pro')" //工艺入库单
				+ " OR (StorageLedgerDetail.billType = 'GYCK' AND StorageLedgerDetail.storageTypeD = 'pro')" //工艺退货单
				
				+ " OR (StorageLedgerDetail.billType = 'GYZY' AND StorageLedgerDetail.storageTypeD = 'pro') " //工艺委外转移单
	//			转移单（移出地为委外供应商：pro，移入地为工作中心:wor）
				+ ")"
				;
		
		Query query = em.createQuery(kSql);
		query.setParameter("dateStart", dateStart);
		query.setParameter("dateEnd", dateEnd);
		query.setParameter("enterpriseNo", enterpriseNo);
		@SuppressWarnings("unchecked")
		List<StorageLedgerDetail> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public List<StorageLedgerDetail> getCostingRKDListByWorkNo(Date dateStart, Date dateEnd, String workNo, String enterpriseNo) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageLedgerDetail"
				+ " FROM StorageLedgerDetail StorageLedgerDetail "

				+ " WHERE StorageLedgerDetail.workNo = :workNo "
				+ " AND StorageLedgerDetail.orderDate >= :dateStart "
				+ " AND StorageLedgerDetail.orderDate <= :dateEnd "
				+ " AND StorageLedgerDetail.enterpriseNo = :enterpriseNo "
				
				+ " AND ( StorageLedgerDetail.billType = 'WWRK'" //委外入库单
				+ " OR StorageLedgerDetail.billType = 'WWCK'" //委外退货单
				
				+ " OR StorageLedgerDetail.billType = 'SCRK'" //生产入库单
				+ " OR StorageLedgerDetail.billType = 'SCCK'" //生产退货单
				+ " OR StorageLedgerDetail.billType = 'GYRK'" //工艺入库单
				+ " OR StorageLedgerDetail.billType = 'GYCK'" //工艺退货单
				
				//上面billType相当于
				//+ " OR StorageLedgerDetail.costingType = 'S'" //成本来源 
				
				+ ")"
				;
		Query query = em.createQuery(kSql);
		query.setParameter("workNo", workNo);
		query.setParameter("dateStart", dateStart);
		query.setParameter("dateEnd", dateEnd);
		query.setParameter("enterpriseNo", enterpriseNo);
		@SuppressWarnings("unchecked")
		List<StorageLedgerDetail> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public List<StorageLedgerDetail> getOrderZYDByOrigin(String origin, Date dateStart, Date dateEnd,
			String enterpriseNo) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageLedgerDetail "
				+ "FROM StorageLedgerDetail StorageLedgerDetail "

				+ " WHERE StorageLedgerDetail.orderDate >= :dateStart "
				+ " AND StorageLedgerDetail.orderDate <= :dateEnd "
				+ " AND StorageLedgerDetail.enterpriseNo = :enterpriseNo "
				
				+ " AND ( StorageLedgerDetail.billType = 'GYTC'" //投料单
				+ " OR StorageLedgerDetail.billType = 'GYZY'" //转移单
				+ " OR StorageLedgerDetail.billType = 'GYRK')" //工艺入库单
				+ " AND ( StorageLedgerDetail.origin = :origin ) " //来源
				;
		Query query = em.createQuery(kSql);
		query.setParameter("origin", origin);
		query.setParameter("dateStart", dateStart);
		query.setParameter("dateEnd", dateEnd);
		query.setParameter("enterpriseNo", enterpriseNo);
		@SuppressWarnings("unchecked")
		List<StorageLedgerDetail> resultList = query.getResultList();
		return resultList;
	}



	
}
